package cn.edu.bzu.aaa.dao;

import cn.edu.bzu.aaa.model.ReimbursementApproval;
import cn.edu.bzu.aaa.until.C3P0Util;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class ReimbursementApprovalDAO {

    /**
     * 获取所有报销审批记录。
     *
     * @return 包含所有报销审批记录的列表。
     */
    public List<ReimbursementApproval> getAll() {
        List<ReimbursementApproval> list = new ArrayList<>();
        try (Connection conn = C3P0Util.getConnection();
             PreparedStatement ps = conn.prepareStatement("SELECT * FROM reimbursementapproval")) {
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                list.add(populateReimbursementApproval(rs));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    /**
     * 根据ID获取特定的报销审批记录。
     *
     * @param id 报销审批记录的ID。
     * @return 对应ID的报销审批记录，如果不存在则返回null。
     */
    public ReimbursementApproval getByID(int id) {
        try (Connection conn = C3P0Util.getConnection();
             PreparedStatement ps = conn.prepareStatement("SELECT * FROM reimbursementapproval WHERE ReimbursementID = ?")) {
            ps.setInt(1, id);
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                return populateReimbursementApproval(rs);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 添加新的报销审批记录。
     *
     * @param ra 新的报销审批记录对象。
     */
    public void add(ReimbursementApproval ra) {
        try (Connection conn = C3P0Util.getConnection();
             PreparedStatement ps = conn.prepareStatement(
                     "INSERT INTO reimbursementapproval (ApprovalID, UserID, Companions, Destination, Summary, Cost, ApprovalStatus) VALUES (?, ?, ?, ?, ?, ?, ?)",
                     Statement.RETURN_GENERATED_KEYS)) {
            ps.setInt(1, ra.getApprovalID());
            ps.setInt(2, ra.getUserID());
            ps.setString(3, ra.getCompanions());
            ps.setString(4, ra.getDestination());
            ps.setString(5, ra.getSummary());
            ps.setDouble(6, ra.getCost());
            ps.setInt(7, ra.getApprovalStatus());

            ps.executeUpdate();

            ResultSet generatedKeys = ps.getGeneratedKeys();
            if (generatedKeys.next()) {
                ra.setReimbursementID(generatedKeys.getInt(1));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 更新现有的报销审批记录。
     *
     * @param ra 要更新的报销审批记录对象。
     */
    public void update(ReimbursementApproval ra) {
        try (Connection conn = C3P0Util.getConnection();
             PreparedStatement ps = conn.prepareStatement(
                     "UPDATE reimbursementapproval SET ApprovalID=?, UserID=?, Companions=?, Destination=?, Summary=?, Cost=?, ApprovalStatus=? WHERE ReimbursementID=?")) {
            ps.setInt(1, ra.getApprovalID());
            ps.setInt(2, ra.getUserID());
            ps.setString(3, ra.getCompanions());
            ps.setString(4, ra.getDestination());
            ps.setString(5, ra.getSummary());
            ps.setDouble(6, ra.getCost());
            ps.setInt(7, ra.getApprovalStatus());
            ps.setInt(8, ra.getReimbursementID());

            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 删除指定ID的报销审批记录。
     *
     * @param id 要删除的报销审批记录的ID。
     */
    public void delete(int id) {
        try (Connection conn = C3P0Util.getConnection();
             PreparedStatement ps = conn.prepareStatement("DELETE FROM reimbursementapproval WHERE ReimbursementID = ?")) {
            ps.setInt(1, id);
            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 从ResultSet中填充并返回一个新的ReimbursementApproval对象。
     *
     * @param rs ResultSet对象，包含一行数据。
     * @return 填充后的ReimbursementApproval对象。
     * @throws SQLException 如果读取ResultSet时发生错误。
     */
    private ReimbursementApproval populateReimbursementApproval(ResultSet rs) throws SQLException {
        ReimbursementApproval ra = new ReimbursementApproval();
        ra.setReimbursementID(rs.getInt("ReimbursementID"));
        ra.setApprovalID(rs.getInt("ApprovalID"));
        ra.setUserID(rs.getInt("UserID"));
        ra.setCompanions(rs.getString("Companions"));
        ra.setDestination(rs.getString("Destination"));
        ra.setSummary(rs.getString("Summary"));
        ra.setCost(rs.getDouble("Cost"));
        ra.setApprovalStatus(rs.getInt("ApprovalStatus"));
        return ra;
    }
}